﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Serialization;

namespace CommTool
{
   public class ExceptionHandling
    {
        /// <summary>
        /// 将结构化的数据信息转换为规格化串
        /// </summary>
        /// <param name="Jxa"></param>
        /// <param name="Jxb"></param>
        /// <param name="Jxc"></param>
        /// <returns></returns>
        public static String FormatCabinetLayer(int Jxa, int Jxb, int Jxc)
        {
            return String.Format("{0:D2}-{1:D2}-{2:D2}", Jxa, Jxb, Jxc);
        }
        /// <summary>
        /// 输入限制，屏蔽非法按键
        /// </summary>
        /// <param name="e"></param>
        /// <param name="strInput">已经输入的字符</param>
        /// <param name="Isinteger">是否是整型</param>
        /// <param name="IsNagetive">是否允许是负数</param>
        public static void KeysEnterRestrict(System.Windows.Forms.KeyPressEventArgs e, string strInput, bool Isinteger, bool IsNagetive)
        {
            //屏蔽非法按键
            //3、如果不是整数，如果是小数点，如果小数点不在第一位，如果小数点前不是 -号，如果之前没有输入小数点
            //4、如果可以是负数，如果当前输入是第一个符号，
            if (!((e.KeyChar >= '0' && e.KeyChar <= '9')
                || (int)e.KeyChar == (int)System.Windows.Forms.Keys.Back
                || (!Isinteger && (e.KeyChar == '.' && strInput.Length > 0 && strInput[strInput.Length - 1] != '-' && !strInput.Contains(".")))
                || (IsNagetive && e.KeyChar == '-')))
            {
                e.Handled = true;
            }
        }

        /// <summary>
        /// 输出字符格式判断
        /// </summary>
        /// <param name="inputString"></param>
        public static bool InputNumberJudge(string inputString)
        {
            for (int i = 0; i <= inputString.Length - 1; i++)//是否有字符
            {
                if ((inputString[i] < 48 || inputString[i] > 57))
                    return false;//字符
            }
            return true;
        }

        /// <summary>
        /// 把 int类型转换成4个byte数据，添加到byte数组中
        /// </summary>
        /// <param name="pack"></param>
        /// <param name="beginIndex">pack的起始位</param>
        /// <param name="data">要转化的数据</param>
        /// <param name="dataLen">数据长度，字节为单位</param>
        public static void IntToByteArray(ref byte[] data, ref int beginIndex, int value)
        {
            //data[beginIndex++] = (byte)(value);
            //data[beginIndex++] = (byte)(value >> 8);
            //data[beginIndex++] = (byte)(value >> 16);
            //data[beginIndex++] = (byte)(value >> 24);

            BitConverter.GetBytes(value).CopyTo(data, beginIndex);
            beginIndex += 4;
        }
        public static void CatchGlobalException()
        {
            Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);  //处理未捕获的异常

            Application.ThreadException += new System.Threading.ThreadExceptionEventHandler(Application_ThreadException);//处理UI线程异常

            AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);//处理非UI线程异常

        }
        /// <summary>
		/// 错误信息
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		static void Application_ThreadException(object sender, ThreadExceptionEventArgs e)
        {
            string str;
            var strDateInfo = "出现应用程序未处理的异常：" + DateTime.Now + "\r\n";
            var error = e.Exception;
            if (error != null)
            {
                str = string.Format(strDateInfo + "异常类型：{0}\r\n异常消息：{1}\r\n异常信息：{2}\r\n", error.GetType().Name, error.Message, error.StackTrace);
            }
            else
            {
                str = string.Format("应用程序线程错误：{0}", e);
            }
            WriteLog(str);
            MessageBox.Show("发生错误，请查看程序日志！错误日志路径在项目的product-Debug-log-ErrLog中", "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            Environment.Exit(0);
        }

        static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
        {
            var error = e.ExceptionObject as Exception;
            var strDateInfo = "出现应用程序未处理的异常：" + DateTime.Now + "\r\n";
            var str = error != null ? string.Format(strDateInfo + "Application UnhandledException:{0};\n\r堆栈信息:{1}", error.Message, error.StackTrace) : string.Format("Application UnhandledError:{0}", e);

            WriteLog(str);
            MessageBox.Show("发生错误，请查看程序日志！错误日志路径在项目的product-Debug-log-ErrLog中", "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            Environment.Exit(0);
        }

        //static void WriteLog(string str)
        //{
        //    if (!Directory.Exists("ErrLog"))
        //    {
        //        Directory.CreateDirectory("ErrLog");
        //    }

        //    //using (var sw = new StreamWriter(@"..\product\Debug\ErrLog\ErrLog.txt", true))
        //    using (var sw = new StreamWriter(@"ErrLog\ErrLog.txt", true))
        //    {
        //        sw.WriteLine(str);
        //        sw.WriteLine("--------------------------------------------------------------------");
        //        sw.Close();
        //    }
        //}
        /// <summary>
        /// 程序写日记到指定文件夹
        /// </summary>
        /// <param name="log"></param>
        internal static void WriteLog(string log)
        {
            try
            {
                string path = Path.Combine(Application.StartupPath, "log", "ErrLog");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                File.AppendAllText(Path.Combine(path, DateTime.Now.ToString("yyyy-MM-dd HH_mm_ss")), log + Environment.NewLine);
            }
            finally
            {

            }
        }

        /// <summary>
        /// 把 long 类型转换成8个byte数据，添加到byte数组中
        /// </summary>
        /// <param name="pack"></param>
        /// <param name="beginIndex">pack的起始位</param>
        /// <param name="data">要转化的数据</param>
        /// <param name="dataLen">数据长度，字节为单位</param>
        public static void LongToByteArray(ref byte[] data, ref int beginIndex, long value)
        {
            //data[beginIndex++] = (byte)(value);
            //data[beginIndex++] = (byte)(value >> 8);
            //data[beginIndex++] = (byte)(value >> 16);
            //data[beginIndex++] = (byte)(value >> 24);

            BitConverter.GetBytes(value).CopyTo(data, beginIndex);
            beginIndex += 8;
        }
        /// <summary>
        /// 把ushort类型转换成2个byte数据，添加到byte数组中
        /// </summary>
        /// <param name="pack"></param>
        /// <param name="beginIndex">pack的起始位</param>
        /// <param name="data">要转化的数据</param>
        /// <param name="dataLen">数据长度，字节为单位</param>
        public static void ShortToByteArray(ref byte[] data, ref int beginIndex, ushort value)
        {
            //data[beginIndex++] = (byte)(value);
            //data[beginIndex++] = (byte)(value >> 8);

            BitConverter.GetBytes(value).CopyTo(data, beginIndex);
            beginIndex += 2;
        }
        //public static string byteToHexStr(byte[] bytes)
        //{

        //    StringBuilder sb = new StringBuilder();
        //    if (bytes != null && bytes.Length > 0)
        //    {
        //        for (int i = 0; i < bytes.Length; i++)
        //        {
        //            sb.Append(bytes[i].ToString("X2")).Append(" ");
        //        }
        //    }
        //    return sb.ToString();
        //}
        /// <summary>
        /// 获取系统运行目录
        /// </summary>
        /// <returns>系统运行目录路径</returns>
        public static string GetSystemRuningPath()
        {
            String rootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
            rootPath = rootPath.Replace(@"file:\", "");
            return rootPath + "\\";
        }

        /// <summary>
        /// 用NPOI将数据表导出至EXCEL
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="savePath">保存路径</param>
        public static bool NPOIDataToExcel(DataTable dt, string path)
        {
            try
            {
                NPOIExcel excel = new NPOIExcel();
                excel.CreateSheet(dt.Columns);
                foreach (DataRow row in dt.Rows)
                {
                    excel.SetRowValue(row);
                }
                excel.SaveAs(path);
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 用COM将DataTable导出至Excel（8.0）文件
        /// </summary>
        /// <param name="dtData">数据表</param>
        /// <param name="savePath">保存路径</param>
        /// <returns></returns>
        public static bool DataTableExportToExcel80(DataTable dataTable, string savePath)
        {

            if (File.Exists(savePath))
            {
                try
                {
                    File.Delete(savePath);
                }
                catch
                {
                    return false;
                }
            }
            bool result = true;
            string TableName = "DataTable";
            dataTable.TableName = TableName;
            string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + savePath + ";Extended Properties=Excel 8.0";

            //连接
            OleDbConnection myConn = new OleDbConnection(strCon);
            try
            {
                string strCom = "select * from [" + TableName + "$]";
                myConn.Open();
                string ctString = string.Empty;
                ctString = "CREATE TABLE [" + TableName + "](";

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    if (i != dataTable.Columns.Count - 1)
                        ctString += dataTable.Columns[i].ToString() + " CHAR(50),";
                    else
                        ctString += dataTable.Columns[i].ToString() + " CHAR(50)";
                }
                ctString += ")";

                using (OleDbCommand cmd = new OleDbCommand(ctString, myConn))
                {
                    if (myConn.State != ConnectionState.Open) myConn.Open();
                    cmd.ExecuteNonQuery();
                }

                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
                builder.QuotePrefix = "[";
                builder.QuoteSuffix = "]";
                DataSet newds = new DataSet();
                newds.Tables.Add(dataTable);
                myCommand.Update(newds, TableName);
                newds.Tables.Remove(dataTable);
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                myConn.Close();
            }

            return result;
        }

        /// <summary>
        /// 读Excel（8.0）文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataTable ReadExcel80(String fileName, String tableName)
        {
            OleDbConnection oleDbConnc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;");
            oleDbConnc.Open();
            //DataTable schemaTable = oleDbConnc.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            //if(schemaTable.Columns.Count < 2)
            //    return null;
            //string tableName = schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            String sql = "select * from [" + tableName + "]";
            OleDbCommand objCmd = new OleDbCommand(sql, oleDbConnc);
            OleDbDataAdapter myData = new OleDbDataAdapter(sql, oleDbConnc);
            DataSet ds = new DataSet();
            myData.Fill(ds, tableName);//填充数据
            oleDbConnc.Close();
            return ds.Tables[tableName];
        }

        public static bool NPOIDataToExcelSheets2003(DataTable dataTable, string path, string sheetName, out String errDesc)
        {
            if (dataTable == null || dataTable.Columns.Count == 0)
            {
                errDesc = "数据源中没有列信息！";
                return false;
            }
            if (path == null)
            {
                errDesc = "无效路径！";
                return false;
            }
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                cellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;

                Int32 rowNumEverySheet = 65000;
                Int32 sheetNum = dataTable.Rows.Count / rowNumEverySheet;
                Int32 sheetIndex = 0;
                for (; sheetIndex < sheetNum; sheetIndex++)
                {
                    ISheet infoSheet = workbook.CreateSheet(String.Format("{0}{1}", sheetName, sheetIndex + 1));
                    IRow dataRow = null;
                    List<String> ListColumnCaption = new List<string>();
                    for (Int32 columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        ListColumnCaption.Add(dataTable.Columns[columnIndex].ColumnName);
                    }

                    Int32 Index = 0;
                    dataRow = infoSheet.CreateRow(0);
                    foreach (string columnStr in ListColumnCaption)
                    {
                        ICell cell = dataRow.CreateCell(Index);
                        cell.SetCellValue(columnStr);
                        cell.CellStyle = cellStyle;
                        infoSheet.AutoSizeColumn(Index);
                        Index++;
                    }

                    if (dataRow == null)
                        continue;
                    Int32 rowIndex = sheetIndex * rowNumEverySheet;
                    Int32 rowIndexSheet = 1;
                    for (; rowIndex < sheetIndex * rowNumEverySheet + rowNumEverySheet; rowIndex++)
                    {
                        dataRow = infoSheet.CreateRow(rowIndexSheet);
                        if (dataRow == null)
                            continue;
                        for (Int32 cellIndex = 0; cellIndex < dataTable.Columns.Count; cellIndex++)
                        {
                            String dataInfo = String.Empty;
                            if (dataTable.Rows[rowIndex][cellIndex] != null && dataTable.Rows[rowIndex][cellIndex] != DBNull.Value)
                                dataInfo = dataTable.Rows[rowIndex][cellIndex] as String;
                            else
                                dataInfo = "";

                            double value = 0;
                            if (double.TryParse(dataInfo, out value))
                                dataRow.CreateCell(cellIndex).SetCellValue(value);
                            else
                                dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);

                            //dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                        }
                        rowIndexSheet++;
                    }
                }

                if (dataTable.Rows.Count % rowNumEverySheet > 0)
                {
                    ISheet infoSheet = workbook.CreateSheet(String.Format("{0}{1}", sheetName, sheetIndex + 1));
                    IRow dataRow = null;
                    List<String> ListColumnCaption = new List<string>();
                    for (Int32 columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        ListColumnCaption.Add(dataTable.Columns[columnIndex].ColumnName);
                    }

                    Int32 Index = 0;
                    dataRow = infoSheet.CreateRow(0);
                    foreach (string columnStr in ListColumnCaption)
                    {
                        ICell cell = dataRow.CreateCell(Index);
                        cell.SetCellValue(columnStr);
                        cell.CellStyle = cellStyle;
                        infoSheet.AutoSizeColumn(Index);
                        Index++;
                    }
                    if (dataRow == null)
                    {
                        errDesc = "导出失败";
                        return false;
                    }
                    Int32 rowIndex = sheetIndex * rowNumEverySheet;
                    Int32 rowIndexSheet = 1;
                    Int32 rowTotalNum = sheetIndex * rowNumEverySheet + dataTable.Rows.Count - sheetIndex * rowNumEverySheet;
                    for (; rowIndex < rowTotalNum; rowIndex++)
                    {
                        dataRow = infoSheet.CreateRow(rowIndexSheet);
                        if (dataRow == null)
                            continue;
                        for (Int32 cellIndex = 0; cellIndex < dataTable.Columns.Count; cellIndex++)
                        {
                            String dataInfo = String.Empty;
                            if (dataTable.Rows[rowIndex][cellIndex] != null && dataTable.Rows[rowIndex][cellIndex] != DBNull.Value)
                                dataInfo = dataTable.Rows[rowIndex][cellIndex] as String;
                            else
                                dataInfo = "";

                            double value = 0;
                            if (double.TryParse(dataInfo, out value))
                                dataRow.CreateCell(cellIndex).SetCellValue(value);
                            else
                                dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);

                            //dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                        }
                        rowIndexSheet++;
                    }
                }

                //保存   
                //                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                    }
                }
            }
            catch (Exception exportException)
            {
                //throw exportException;
                errDesc = exportException.Message;
                return false;
            }
            errDesc = "导出成功";
            return true;
        }

        /// <summary>
        /// 深圳拓邦，把数据导出为一个Excel的不同sheet页
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <param name="errDesc"></param>
        /// <returns></returns>
        public static bool NPOIDataToExcelSheets2003(DataSet dataSet, string path, out String errDesc)
        {
            if (dataSet == null || dataSet.Tables.Count == 0)
            {
                errDesc = "数据源中没有列信息！";
                return false;
            }
            if (path == null)
            {
                errDesc = "无效路径！";
                return false;
            }
            int s = 0;
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                cellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;

                int rowNumEverySheet = 65000;
                int i = 0;
                foreach (DataTable tb in dataSet.Tables)
                {
                    int sheetNum = (tb.Rows.Count + 1) / rowNumEverySheet;  // 计算
                    if (tb.Rows.Count % rowNumEverySheet > 0)
                    {
                        sheetNum++;
                    }
                    for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
                    {
                        //创建sheet页
                        string sheetName = string.IsNullOrEmpty(tb.TableName) ? ("Table_" + i.ToString()) : tb.TableName;   // sheet页名称
                        sheetName = sheetNum > 1 ? String.Format("{0}({1})", tb.TableName, sheetIndex) : sheetName;          // 当一个table的sheet页大于1页时，sheet页名称+页索引                        
                        ISheet infoSheet = workbook.CreateSheet(sheetName);
                        s++;
                        i++;
                        // 表标题
                        IRow dataRow = infoSheet.CreateRow(0);
                        if (dataRow == null)
                            continue;
                        for (Int32 columnIndex = 0; columnIndex < tb.Columns.Count; columnIndex++)
                        {
                            string caption = tb.Columns[columnIndex].ColumnName ?? ("列" + columnIndex.ToString());
                            ICell cell = dataRow.CreateCell(columnIndex);
                            cell.SetCellValue(caption);
                            cell.CellStyle = cellStyle;
                            //infoSheet.AutoSizeColumn(columnIndex);
                        }

                        // 表数据
                        int rowIndexSheet = 1;
                        for (int rowIndex = sheetIndex * rowNumEverySheet; rowIndex < tb.Rows.Count && rowIndexSheet <= rowNumEverySheet; rowIndex++)
                        {
                            dataRow = infoSheet.CreateRow(rowIndexSheet);
                            if (dataRow == null)
                                continue;
                            for (Int32 cellIndex = 0; cellIndex < tb.Columns.Count; cellIndex++)
                            {
                                String dataInfo = String.Empty;
                                if (tb.Rows[rowIndex][cellIndex] != null && tb.Rows[rowIndex][cellIndex] != DBNull.Value)
                                    dataInfo = tb.Rows[rowIndex][cellIndex] as String;
                                else
                                    dataInfo = "";

                                dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                            }
                            rowIndexSheet++;
                        }
                    }
                }
                using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);
                }
            }
            catch (Exception exportException)
            {
                errDesc = exportException.Message;
                return false;
            }
            errDesc = "导出成功";
            return true;
        }
        /// <summary>
        /// BYD数据统计专用
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <param name="errDesc"></param>
        /// <returns></returns>
        public static bool NPOIDataToExcelSheets(DataTable dataTable, string path, string sheetName, IList<String> fileHeadList, out String errDesc)
        {
            if (dataTable == null || dataTable.Columns.Count == 0)
            {
                errDesc = "数据源中没有列信息！";
                return false;
            }
            if (path == null)
            {
                errDesc = "无效路径！";
                return false;
            }
            //if (fileHeadList == null || fileHeadList.)
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                cellStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                cellStyle.WrapText = true;



                Int32 rowNumEverySheet = 65000;
                Int32 sheetNum = dataTable.Rows.Count / rowNumEverySheet;
                Int32 sheetIndex = 0;
                for (; sheetIndex < sheetNum; sheetIndex++)
                {
                    ISheet infoSheet = workbook.CreateSheet(String.Format("{0}{1}", sheetName, sheetIndex + 1));
                    IRow dataRow = null;
                    List<String> ListColumnCaption = new List<string>();
                    for (Int32 columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        ListColumnCaption.Add(dataTable.Columns[columnIndex].ColumnName);
                    }


                    //添加数据统计excel文件头，工艺名称等等
                    Int32 Index = 0;
                    dataRow = infoSheet.CreateRow(0);
                    foreach (String strIndex in fileHeadList)
                    {
                        ICell fileHeadCell = dataRow.CreateCell(Index);
                        fileHeadCell.SetCellValue(strIndex);
                        fileHeadCell.CellStyle = cellStyle;
                        Index++;
                    }

                    Index = 0;
                    dataRow = infoSheet.CreateRow(1);
                    //dataRow.Height = 200;
                    foreach (string columnStr in ListColumnCaption)
                    {
                        ICell cell = dataRow.CreateCell(Index);
                        cell.SetCellValue(columnStr);
                        cell.CellStyle = cellStyle;

                        infoSheet.AutoSizeColumn(Index);

                        Index++;
                    }

                    if (dataRow == null)
                        continue;
                    Int32 rowIndex = sheetIndex * rowNumEverySheet;
                    Int32 rowIndexSheet = 2;
                    for (; rowIndex < sheetIndex * rowNumEverySheet + rowNumEverySheet; rowIndex++)
                    {
                        dataRow = infoSheet.CreateRow(rowIndexSheet);
                        if (dataRow == null)
                            continue;
                        for (Int32 cellIndex = 0; cellIndex < dataTable.Columns.Count; cellIndex++)
                        {
                            String dataInfo = String.Empty;
                            if (dataTable.Rows[rowIndex][cellIndex] != null && dataTable.Rows[rowIndex][cellIndex] != DBNull.Value)
                                dataInfo = dataTable.Rows[rowIndex][cellIndex] as String;
                            else
                                dataInfo = "";

                            double value = 0;
                            if (double.TryParse(dataInfo, out value))
                                dataRow.CreateCell(cellIndex).SetCellValue(value);
                            else
                                dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                        }
                        rowIndexSheet++;
                    }

                }

                if (dataTable.Rows.Count % rowNumEverySheet > 0)
                {
                    ISheet infoSheet = workbook.CreateSheet(String.Format("{0}{1}", sheetName, sheetIndex + 1));
                    IRow dataRow = null;
                    List<String> ListColumnCaption = new List<string>();
                    for (Int32 columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                    {
                        ListColumnCaption.Add(dataTable.Columns[columnIndex].ColumnName);
                    }

                    //添加数据统计excel文件头，工艺名称等等
                    Int32 Index = 0;
                    dataRow = infoSheet.CreateRow(0);
                    foreach (String strIndex in fileHeadList)
                    {
                        ICell fileHeadCell = dataRow.CreateCell(Index);
                        fileHeadCell.SetCellValue(strIndex);
                        fileHeadCell.CellStyle = cellStyle;


                        Index++;
                    }

                    Index = 0;
                    dataRow = infoSheet.CreateRow(1);
                    foreach (string columnStr in ListColumnCaption)
                    {
                        ICell cell = dataRow.CreateCell(Index);
                        cell.SetCellValue(columnStr);
                        cell.CellStyle = cellStyle;
                        infoSheet.AutoSizeColumn(Index);
                        Index++;
                    }
                    if (dataRow == null)
                    {
                        errDesc = "导出失败";
                        return false;
                    }
                    Int32 rowIndex = sheetIndex * rowNumEverySheet;
                    Int32 rowIndexSheet = 2;
                    Int32 rowTotalNum = sheetIndex * rowNumEverySheet + dataTable.Rows.Count - sheetIndex * rowNumEverySheet;
                    for (; rowIndex < rowTotalNum; rowIndex++)
                    {
                        dataRow = infoSheet.CreateRow(rowIndexSheet);
                        if (dataRow == null)
                            continue;
                        for (Int32 cellIndex = 0; cellIndex < dataTable.Columns.Count; cellIndex++)
                        {
                            String dataInfo = String.Empty;
                            if (dataTable.Rows[rowIndex][cellIndex] != null && dataTable.Rows[rowIndex][cellIndex] != DBNull.Value)
                                dataInfo = dataTable.Rows[rowIndex][cellIndex] as String;
                            else
                                dataInfo = "";

                            double value = 0;
                            if (double.TryParse(dataInfo, out value))
                                dataRow.CreateCell(cellIndex).SetCellValue(value);
                            else
                                dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                            //dataRow.CreateCell(cellIndex).SetCellValue(dataInfo);
                        }
                        rowIndexSheet++;
                    }

                }

                //保存   
                using (MemoryStream ms = new MemoryStream())
                {
                    using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
                    {
                        workbook.Write(fs);
                    }
                }
            }
            catch (Exception exportException)
            {
                //throw exportException;
                errDesc = exportException.Message;
                return false;
            }
            errDesc = "导出成功";
            return true;
        }

        ///

        /// 将DataTable中数据写入到CSV文件中
        ///
        /// 提供保存数据的DataTable
        /// CSV的文件路径
        public static void SaveToCSV(DataTable dt, string fileName)
        {
            if (dt == null)
                return;
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
            string data = "";
            //写出列名称
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                data += dt.Columns[i].ColumnName.ToString();
                if (i < dt.Columns.Count - 1)
                {
                    data += ",";
                }
            }
            sw.WriteLine(data);
            //写出各行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    data += (dt.Rows[i][j] == DBNull.Value || dt.Rows[i][j] == null) ? " " : dt.Rows[i][j].ToString();
                    if (j < dt.Columns.Count - 1)
                    {
                        data += ",";
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
            fs.Dispose();
            sw.Dispose();
        }

        /// <summary>
        /// 把DataTable 导出到txt文件
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="fileName">txt文件名</param>
        /// <param name="columnWidth">每一数据列的宽度，以空格算计</param>
        public static void SaveToText(DataTable dt, string fileName, List<int> columnWidth)
        {
            if (dt == null)
                return;
            FileStream fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
            string data = "";
            //写出列名称
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string colName = dt.Columns[i].ColumnName.ToString();
                int colLen = System.Text.Encoding.Default.GetBytes(colName).Length;
                if (colLen < columnWidth[i])
                {
                    colName = colName.PadRight(columnWidth[i]);
                    data += colName;
                }
                else
                {
                    data += colName;
                    data += "    ";
                }
            }
            sw.WriteLine(data);
            //写出各行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] == DBNull.Value || dt.Rows[i][j] == null)
                    {
                        data += ("".PadRight(columnWidth[j]));
                    }
                    else
                    {
                        string val = dt.Rows[i][j].ToString();
                        if (val.Length >= columnWidth[j])
                        {
                            data += "    ";
                        }
                        else
                        {
                            val = val.PadRight(columnWidth[j]);
                        }
                        data += val;
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="numberList"></param>
        /// <param name="number"></param>
        /// <returns></returns>
        public static bool ExistNumber(IList<Int32> numberList, Int32 number)
        {
            if (numberList == null || numberList.Count <= 0)
                return false;
            for (int i = 0; i < numberList.Count; i++)
            {
                if (numberList[i] == number)
                    return true;
            }
            return false;
        }

        /// <summary>
        /// 获取指定字节的高4位
        /// </summary>
        /// <param name="inputValue">指定的byte数据</param>
        /// <returns>高4位值</returns>
        public static byte GetHighNibble(byte inputValue)
        {
            //return (byte)((inputValue >> 4) & 0xf0);
            return (byte)((inputValue & 0xf0) >> 4);
        }

        /// <summary>
        /// 获取指定字节的低4位
        /// </summary>
        /// <param name="inputValue"></param>
        /// <returns>低4位值</returns>
        public static byte GetLowNibble(byte inputValue)
        {
            return (byte)((inputValue) & 0x0f);
        }
        /// <summary>
        /// 设置指定字节的高4位
        /// </summary>
        /// <param name="inputValue">要设置高4位的数据</param>
        /// <param name="setValue">高4位的设置值</param>
        /// <returns>返回设置完高4位后的值</returns>
        public static byte SetHighNibble(byte inputValue, byte setValue)
        {
            return (byte)((inputValue & 0x0f) | (setValue & 0x0f) << 4);
        }
        /// <summary>
        /// 设置指定字节的低4位
        /// </summary>
        /// <param name="inputValue">要设置低4位的数据</param>
        /// <param name="setValue">低4位的设置值</param>
        /// <returns>返回设置完低4位后的值</returns>
        public static byte SetLowNibble(byte inputValue, byte setValue)
        {
            return (byte)((inputValue & 0xf0) | (setValue & 0x0f));
        }

        public static T Clone<T>(T RealObject)
        {
            using (Stream objectStream = new MemoryStream())
            {

                IFormatter formatter = new BinaryFormatter();
                formatter.Serialize(objectStream, RealObject);
                objectStream.Seek(0, SeekOrigin.Begin);
                return (T)formatter.Deserialize(objectStream);
            }
        }

        public static bool CheckIPAddress(string ip)
        {
            if (!Regex.IsMatch(ip,
            @"^(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])
           \.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])
           \.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])
           \.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])$", RegexOptions.IgnorePatternWhitespace))
            {

                return false;
            }
            return true;
        }

        /// <summary>
        /// 字节数组转换成十六进制字符串
        /// </summary>
        /// <param name="bytes">要转换的字节数组</param>
        /// <returns></returns>
        public static string ByteArrayToHexStr(byte[] byteArray, bool space)
        {
            if (byteArray == null)
                return "";
            int capacity = space ? byteArray.Length * 3 : byteArray.Length * 2;
            StringBuilder sb = new StringBuilder(capacity);

            if (byteArray != null)
            {
                for (int i = 0; i < byteArray.Length; i++)
                {
                    sb.Append(byteArray[i].ToString("X2"));
                    if (space)
                    {
                        sb.Append(" ");
                    }
                }
            }
            return sb.ToString();
        }
        #region 反序列化
        /// <summary>
        /// 反序列化
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="xml">XML字符串</param>
        /// <returns></returns>
        public static object Deserialize(Type type, string xml)
        {
            try
            {
                using (StringReader sr = new StringReader(xml))
                {
                    XmlSerializer xmldes = new XmlSerializer(type);
                    return xmldes.Deserialize(sr);
                }
            }
            catch (Exception)
            {

                return null;
            }
        }
        /// <summary>
        /// 反序列化
        /// </summary>
        /// <param name="type"></param>
        /// <param name="xml"></param>
        /// <returns></returns>
        public static object Deserialize(Type type, Stream stream)
        {
            XmlSerializer xmldes = new XmlSerializer(type);
            return xmldes.Deserialize(stream);
        }
        #endregion

        #region 序列化
        /// <summary>
        /// 序列化
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="obj">对象</param>
        /// <returns></returns>
        public static string Serializer(Type type, object obj)
        {
            MemoryStream Stream = new MemoryStream();
            XmlSerializer xml = new XmlSerializer(type);
            StreamReader sr = new StreamReader(Stream);
            try
            {
                //序列化对象
                xml.Serialize(Stream, obj);
                Stream.Position = 0;
                string str = sr.ReadToEnd();
                return str;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sr.Close();
                sr.Dispose();
                Stream.Close();
                Stream.Dispose();
            }
        }
        public static byte[] BinarySerialize<T>(T pObj)
        {
            if (pObj == null)
                return null;
            MemoryStream _memory = new MemoryStream();
            BinaryFormatter formatter = new BinaryFormatter();
            try
            {
                formatter.Serialize(_memory, pObj);
                _memory.Position = 0;
                byte[] read = new byte[_memory.Length];
                _memory.Read(read, 0, read.Length);
                return read;
            }
            catch
            {
                return null;
            }
            finally
            {
                _memory.Close();
            }





        }
        public static T BinaryDeserialize<T>(byte[] pBytes)
        {
            T _newOjb = default(T);
            if (pBytes == null)
                return _newOjb;
            MemoryStream _memory = new MemoryStream(pBytes);
            BinaryFormatter formatter = new BinaryFormatter();
            try
            {
                _memory.Position = 0;
                _newOjb = (T)formatter.Deserialize(_memory);
                return _newOjb;
            }
            catch
            {
                return default(T); ;
            }
            finally
            {
                _memory.Close();
            }




        }
        #endregion

        public static bool ArraysEqual<T>(T[] a1, T[] a2)
        {
            if (ReferenceEquals(a1, a2))
                return true;

            if (a1 == null || a2 == null)
                return false;

            if (a1.Length != a2.Length)
                return false;

            EqualityComparer<T> comparer = EqualityComparer<T>.Default;
            for (int i = 0; i < a1.Length; i++)
            {
                if (!comparer.Equals(a1[i], a2[i])) return false;
            }
            return true;
        }

        #region  蜂鸣器

        /// <summary>
        ///  PC喇叭蜂鸣
        /// </summary>
        /// <param name="dwFreq">音调</param>
        /// <param name="dwDuration">蜂鸣时间（单位为毫秒）</param>
        /// <returns></returns>
        [DllImport("kernel32.dll")]
        private static extern int Beep(int dwFreq, int dwDuration);

        public static void VoiceAlarm()
        {
            Beep(2000, 500);
        }
        #endregion

        #region 播放音乐
        /// <summary>
        /// 播放一次音乐
        /// </summary>
        /// <param name="player"></param>
        /// <param name="musicPath"></param>
        public static void PlayMusic(System.Media.SoundPlayer player, string musicPath)
        {
            if (player == null)
                return;
            if (string.IsNullOrWhiteSpace(musicPath) || File.Exists(musicPath) == false)
                return;
            try
            {
                player.SoundLocation = musicPath;
                player.Play();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.Print(e.Message);
            }


        }

        /// <summary>
        /// 循环播放音乐
        /// </summary>
        /// <param name="player"></param>
        /// <param name="musicPath"></param>
        public static void PlayLoopingMusic(System.Media.SoundPlayer player, string musicPath)
        {
            if (player == null)
                return;
            if (string.IsNullOrWhiteSpace(musicPath) || File.Exists(musicPath) == false)
                return;
            try
            {
                player.SoundLocation = musicPath;
                player.PlayLooping();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.Print(e.Message);
            }


        }
        /// <summary>
        /// 停止播放音乐
        /// </summary>
        /// <param name="player"></param>
        /// <param name="musicPath"></param>
        public static void StopPlayMusic(System.Media.SoundPlayer player)
        {
            if (player == null)
                return;
            //player.SoundLocation = musicPath;
            player.Stop();

        }
        #endregion


    }
}
